using System;
using System.Collections.Generic;
using Microsoft.Office.Interop.Excel;

namespace IFS_Gradebook_Generator.App_Code
{
    internal class ExcelClass
    {
        public static void CreateGradebook(string grade, List<Students> lstStudents, ref int filesCreated)
        {
            Application excelApp = new ApplicationClass {Visible = true};

            string workbookPath = ConfigurationData.TemplateFile;

            if (!workbookPath.Contains(".xls"))
                throw new Exception("Invalid excel template gradebook.");

            Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0,
                                                             false, 5, "", "", false, XlPlatform.xlWindows, "", true,
                                                             false, 0, true, false, false);

            Sheets excelSheets = excelWorkbook.Worksheets;

            var excelWorksheet = (Worksheet) excelSheets.get_Item(ConfigurationData.DataEntrySheetName);

            if (excelWorksheet == null)
                throw new Exception("Invalid Data Entry Sheet name");

            Range excelCell = excelWorksheet.get_Range("A2", "A2");
            excelCell.Value2 = "Grade " + grade;

            for (int i = 0; i < lstStudents.Count; i++)
            {
                excelCell = excelWorksheet.get_Range("B" + (i + 4), "B" + (i + 4));
                excelCell.Value2 = string.Format("{0}, {1}", lstStudents[i].LastName, lstStudents[i].FirstName);

                excelCell = excelWorksheet.get_Range("W" + (i + 4), "W" + (i + 4));
                excelCell.Value2 = lstStudents[i].PhoneNumber;
                try
                {
                    excelCell.NumberFormat = "[<=9999999]###-####;(###) ###-####";
                }
                catch(Exception){}
            }

            if (lstStudents.Count <= 35)
            {
                Range rng = excelWorksheet.get_Range("A" + (lstStudents.Count + 4), "A38");
                rng.EntireRow.Delete(XlDirection.xlUp);

                excelWorksheet = (Worksheet)excelSheets.get_Item(ConfigurationData.CalculatedMidYearSheetName);
                TrimAndProtectWorksheet(excelWorksheet, lstStudents.Count);

                excelWorksheet = (Worksheet) excelSheets.get_Item(ConfigurationData.CalculatedFinalSheetName);
                TrimAndProtectWorksheet(excelWorksheet, lstStudents.Count);                
            }

            excelWorkbook.SaveCopyAs(ConfigurationData.GradebookFolder + "IFSS Gradebook " + grade + ".xls");
            excelWorkbook.Close(false, string.Format("IFSS Gradebook {0}.xls", grade), false);

            excelApp.Quit();        
            filesCreated++;

            KillExcel(excelApp); //still doesn't seem to work
        }

        private static void TrimAndProtectWorksheet(_Worksheet excelWorksheet, int numStudents)
        {
            Range rng;
            if (excelWorksheet != null)
            {
                rng = excelWorksheet.get_Range("A" + (numStudents + 4), "A38");
                rng.EntireRow.Delete(XlDirection.xlUp);

                if (ConfigurationData.ProtectWorkSheet)
                    excelWorksheet.Protect(ConfigurationData.SheetProtectionPassword, Type.Missing,
                                           Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                           Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                           Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            }
        }

        protected static void KillExcel(Application exc)
        {
            try
            {
                var wbs = new List<Workbook>();
                var wss = new List<Worksheet>();
                foreach (Workbook wb in exc.Workbooks)
                {
                    foreach (Worksheet ws in wb.Worksheets)
                        wss.Add(ws); // collect worksheets
                    wbs.Add(wb); // collect workbooks
                }
                for (int i = 0; i < wss.Count; i++)
                {
                    wss[i].Delete();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(wss[i]); // release it
                    wss[i] = null; // null it
                }
                for (int i = 0; i < wbs.Count; i++)
                {
                    wbs[i].Close(null, null, null);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(wbs[i]); // release it
                    wbs[i] = null; // null it
                }
                exc.Workbooks.Close(); // so you can close this
                exc.Quit(); // so you can quit this
                System.Runtime.InteropServices.Marshal.ReleaseComObject(exc); // release it
                exc = null;
                GC.Collect(); // this sets up the finalizers
                GC.WaitForPendingFinalizers();
                GC.Collect(); //apparently this kills it
                GC.WaitForPendingFinalizers();
            }
            catch {}
        } 
    }
}